﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using System.Text.RegularExpressions;

namespace BoAddonBiz_FreightForwarder.Air
{
    /// <summary>
    /// SEDL数据导出
    /// </summary>
    class B1Form_SEDLExportForm : SAPBoAddon.B1Form
    {
        public B1Form_SEDLExportForm()
        {
            this.frm_ItemEvent += new SAPBoAddon.IB1FormEvents.B1ItemEventEventHandler(B1Form_SEDLExportForm_frm_ItemEvent);
        }
        #region 继承基类
        protected override bool BeforeSaveCheckFormData()
        {
            return true;
        }

        protected override void FormEditModeChange(SAPBoAddon.frmItemEditMode frmEditMode)
        {
        }

        protected override void FormInfoSetting(SAPBoAddon.B1FormSetting FormSetting)
        {
            this.FormSetting.FormType = B1FormTypes.ado_AVA_SEDLExportForm;
            this.FormSetting.FormFileName = "AVA_SEDLExptForm.srf";
        }

        protected override void InitializeForm(SAPBoAddon.B1FormInitializePar InitPar)
        {

        }
        #endregion
        void B1Form_SEDLExportForm_frm_ItemEvent(SAPBoAddon.B1AddonBase.B1ItemEvent pVal, ref bool BubbleEvent)
        {
            if (pVal.Before_Action)
            {

            }
            else
            {
                if (pVal.ItemUID == "expxlsx" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED)
                {
                    this.ExportExcelFile();
                }
                if (pVal.ItemUID == "expxml" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED)
                {
                    this.ExportXML();
                }
                //新增 调单时间 列
                if (pVal.ItemUID == "expRxls" && pVal.EventType == SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED)
                {
                    this.ExportReleaseTimeExcelFile();
                }
            }
        }

        #region 私有函数
        private string GetConditionofCreateDate()
        {
            string str_con = "WHERE 1=1";
            string Date_Begin = SAPBoAddon.B1Assistant.Item.GetValue(this.CurrentForm, "4").ToString().Trim();
            string Date_End = SAPBoAddon.B1Assistant.Item.GetValue(this.CurrentForm, "6").ToString().Trim();
            if (!string.IsNullOrEmpty(Date_Begin))
            {
                str_con = str_con + string.Format("AND ISNULL(T1.U_ATD,N'')>=N'{0}' ", Date_Begin);
            }
            if (!string.IsNullOrEmpty(Date_End))
            {
                str_con = str_con + string.Format("AND ISNULL(T1.U_ATD,N'')<=N'{0}' ", Date_End);
            }
            return str_con;
        }
        private void ExportExcelFile()
        {
            #region 测试代码
            System.DateTime dt1 = DateTime.Now;
            #endregion
            Excel.Application xlApp = new Excel.Application();
            if (xlApp == null)
            {
                SBOApp.MessageBox("未安装Excel");
                return;
            }
            SBOApp.StatusBar.SetText("正在创建文件 ...", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Warning);
            this.CurrentForm.Items.Item("expxlsx").Enabled = false;

            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1    
            Excel.Range range = worksheet.get_Range("A1", "AV2");
            range.Font.Name = "Arial";
            range.Font.Size = 10;
            range.Font.Bold = true;
            range.Cells.Interior.Color = 16777164;//"#CCFFFF";
            range.WrapText = true;
            range.Borders.LineStyle = 1;
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic);

            #region 标题 2
            worksheet.Cells[2, 1] = "MAWB";
            worksheet.Cells[2, 2] = "HAWB";
            worksheet.Cells[2, 3] = "Flight";
            worksheet.Cells[2, 4] = " Container No.	";
            worksheet.Cells[2, 5] = "Origin";
            worksheet.Cells[2, 6] = "Departure";
            worksheet.Cells[2, 7] = "Pieces";
            worksheet.Cells[2, 8] = "GW";
            worksheet.Cells[2, 9] = "CW";
            worksheet.Cells[2, 10] = "Pick up Date";
            worksheet.Cells[2, 11] = "ETD";
            worksheet.Cells[2, 12] = "ATD";
            worksheet.Cells[2, 13] = "ETA";
            worksheet.Cells[2, 14] = "ATA";
            worksheet.Cells[2, 15] = "RELEASE DATE";
            worksheet.Cells[2, 16] = "DELIVERY  DATE";
            worksheet.Cells[2, 17] = "VENDOR";
            worksheet.Cells[2, 18] = "PO No.";
            worksheet.Cells[2, 19] = "DN No.";
            worksheet.Cells[2, 20] = "DN VALUE";
            worksheet.Cells[2, 21] = "FORWARDER";
            worksheet.Cells[2, 22] = "TRANS-MODE";
            worksheet.Cells[2, 23] = "TRADE MODE";
            worksheet.Cells[2, 24] = "REMARK(SEDL)";
            worksheet.Cells[2, 25] = "CDF NO.";
            worksheet.Cells[2, 26] = "DUTY";
            worksheet.Cells[2, 27] = "VAT";
            worksheet.Cells[2, 28] = "FREIGHT CHARGE";
            worksheet.Cells[2, 29] = "INVOICE NO.";
            worksheet.Cells[2, 30] = "POS NO.";
            worksheet.Cells[2, 31] = "TERMINAL CHARGE	";
            worksheet.Cells[2, 32] = "STORAGE/SERVICE FEE	";
            worksheet.Cells[2, 33] = "TRANSPORT";
            worksheet.Cells[2, 34] = "OTHER";
            worksheet.Cells[2, 35] = "ServiceClass";
            worksheet.Cells[2, 36] = "GdsNatu";
            worksheet.Cells[2, 37] = "FCL(TEU)";
            worksheet.Cells[2, 38] = "LCL(CBM)";
            worksheet.Cells[2, 39] = "Cus Broker";
            worksheet.Cells[2, 40] = "CustomClearClass";
            worksheet.Cells[2, 41] = "Currency";
            worksheet.Cells[2, 42] = "Incoterm";
            worksheet.Cells[2, 43] = "Destin";
            worksheet.Cells[2, 44] = "Remark(SeaJet)";
            worksheet.Cells[2, 45] = "Delete(HAWB)";
            worksheet.Cells[2, 46] = "Delete(DN)";
            worksheet.Cells[2, 47] = "VENDOR ID";
            worksheet.Cells[2, 48] = "NOTIFY DATE";
            #endregion

            string fileName = string.Format(@"{0}\Seajet template_V013air freight_{1}.xls", System.Windows.Forms.Application.StartupPath, DateTime.Now.ToString("yyyyMMdd_HHmm"));
            workbook.SaveCopyAs(fileName);

            int recordCount = 0;
            string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;' ", fileName);
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            OleDbCommand cmd = null;
            bool bRet = false;
            DateTime date1900 = Convert.ToDateTime("1900-01-01");
            List<string> fields_empty = new List<string>() { "U_Flight", "U_ContainerNo", "U_StartPort", "U_StartPortENamel", "U_Quantity", "U_GrossWeight", "U_BillingWeight" };
            List<string> List_SBLNumber = new List<string>();
            string SBLNumber = string.Empty;
            bool isExistsHAWB = false;
            try
            {
                string[] ColUIDIndex ={
                "U_MBLNumber","U_SBLNumber","U_Flight","U_ContainerNo","U_StartPort",
                "U_StartPortENamel","U_Quantity","U_GrossWeight","U_BillingWeight","U_PickupDate",
                "U_ETD","U_ATD","U_ETA","U_ATA","U_ReleaseDate",
                "U_DeliveryDate","U_Sender","U_Forwarder","U_TransMode","U_TradeMode",
                "U_Remarks",
                "U_ServiceType","U_CargoType","U_CusBroker","U_CustomClearClass","U_Incoterm",
                "U_EndPortThCode","U_PONo","U_DNNo","U_DNCurrency","U_DNValue",
                "U_VendorID","U_RCDate"
                  };

                string SQL_ConditionDate = this.GetConditionofCreateDate();
                SAPbobsCOM.Recordset oRs = SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
                string SQL_SELECT_SEDLItem = @"SELECT DISTINCT
T1.U_MBLNumber,T1.U_SBLNumber,T1.U_Flight,T1.U_ContainerNo,UPPER(T1.U_StartPort) AS U_StartPort,
UPPER(T1.U_StartPortENamel) AS U_StartPortENamel,T1.U_Quantity,T1.U_GrossWeight,T1.U_BillingWeight,T1.U_PickupDate,
T1.U_ETD,T1.U_ATD,T1.U_ETA,T1.U_ATA,T1.U_ReleaseDate,
T1.U_DeliveryDate,UPPER(T1.U_Sender) as U_Sender,
CASE T1.U_Forwarder 
WHEN N'MUELLER+PARTNER GMBH' THEN N'M+P' 
WHEN N'HELLMANN WORLDWIDE LOGISTICS GMBH & CO KG'  THEN N'HELLMANN'
ELSE T1.U_Forwarder END AS U_Forwarder,
T1.U_TransMode,T1.U_TradeMode,
T1.U_ServiceType,T1.U_CargoType,T1.U_CusBroker,T1.U_CustomClearClass,T1.U_Incoterm,
T1.U_EndPortThCode,
T2.U_PONo,T2.U_DNNo,T2.U_DNCurrency,T2.U_DNValue,
T4.U_VendorID,T1.U_RCDate,cast(T1.U_Remarks as nvarchar(3000)) as U_Remarks
FROM [@AVA_SEDL] T1 LEFT JOIN [@AVA_SEDL1] T2 ON T1.DocEntry=T2.DocEntry 
LEFT JOIN [@AVA_FF_OBNO] T3 ON T1.U_SBLNumber=T3.U_SBLNumber
LEFT JOIN [@AVA_FF_BNO4] T4 ON T3.DocEntry=T4.DocEntry
" + SQL_ConditionDate + " ORDER BY T1.U_PickupDate,T1.U_MBLNumber,T1.U_SBLNumber  ";
                oRs.DoQuery(SQL_SELECT_SEDLItem);

                conn.Open();
                cmd = new OleDbCommand(@"create table [Sheet1$](
                    U_MBLNumber Text,U_SBLNumber Text,U_Flight Text,U_ContainerNo Text,U_StartPortENamel Text,
U_StartPort Text,U_Quantity int,U_GrossWeight double,U_BillingWeight double,U_PickupDate Date,
U_ETD Date,U_ATD Date,U_ETA Date,U_ATA Date,U_ReleaseDate Date,
U_DeliveryDate Date,U_Sender Text,	U_PONo Text,U_DNNo Text,U_DNValue double,	
U_Forwarder Text,U_TransMode Text,U_TradeMode Text,	[REMARK(SEDL)] Text,[CDF NO.] Text,	
[DUTY] Text,	[VAT] double,	[FREIGHT CHARGE] Text,	[INVOICE NO.] Text,	[POS NO.] Text,	
[TERMINAL CHARGE] Text,	[STORAGE/SERVICE FEE] Text,[TRANSPORT] Text,	[OTHER] Text,	U_ServiceType Text,
U_CargoType Text,	[FCL(TEU)] Text,	[LCL(CBM)] Text,U_CusBroker Text,U_CustomClearClass Text,
U_DNCurrency Text,U_Incoterm Text,U_EndPortThCode Text,[Remark(SeaJet)] Text,[Delete(HAWB)] Text,	
[Delete(DN)] Text,U_VendorID Text,U_RCDate Date
                    )", conn);
                cmd.ExecuteNonQuery();

                string strSQL = @"INSERT INTO [Sheet1$] (
U_MBLNumber,U_SBLNumber,U_Flight,U_ContainerNo,U_StartPort,
U_StartPortENamel,U_Quantity,U_GrossWeight,U_BillingWeight,U_PickupDate,
U_ETD,U_ATD,U_ETA,U_ATA,U_ReleaseDate,
U_DeliveryDate,U_Sender,U_Forwarder,U_TransMode,U_TradeMode,
[REMARK(SEDL)],
U_ServiceType,U_CargoType,U_CusBroker,U_CustomClearClass,U_Incoterm,
U_EndPortThCode,U_PONo,U_DNNo,U_DNCurrency,U_DNValue,
U_VendorID,U_RCDate) VALUES (
?, ?, ?,?,?,
?, ?, ?,?,?,
?, ?, ?,?,?,
?, ?, ?,?,?,
?,
?, ?, ?,?,?,
?, ?, ?,?,?,
?,?
)";
                cmd = new OleDbCommand(strSQL, conn);
                foreach (var item in ColUIDIndex)
                {
                    cmd.Parameters.Add(item, OleDbType.VarChar);
                }
                recordCount = oRs.RecordCount;
                for (int i = 0; i < oRs.RecordCount; i++)
                {
                    isExistsHAWB = true;
                    SBLNumber = oRs.Fields.Item("U_SBLNumber").Value;
                    if (!List_SBLNumber.Contains(SBLNumber))
                    {
                        List_SBLNumber.Add(SBLNumber);
                        isExistsHAWB = false;
                    }
                    for (int j = 0; j < ColUIDIndex.Length; j++)
                    {
                        if (isExistsHAWB && fields_empty.Contains(ColUIDIndex[j]))
                        {
                            cmd.Parameters[j].Value = DBNull.Value;
                        }
                        else
                        {
                            if (oRs.Fields.Item(ColUIDIndex[j]).Value.GetType() == typeof(DateTime))
                            {
                                if (oRs.Fields.Item(ColUIDIndex[j]).Value <= date1900)
                                {
                                    cmd.Parameters[j].Value = DBNull.Value;
                                }
                                else
                                {
                                    cmd.Parameters[j].Value = oRs.Fields.Item(ColUIDIndex[j]).Value;
                                }
                            }
                            else
                            {
                                cmd.Parameters[j].Value = oRs.Fields.Item(ColUIDIndex[j]).Value;
                            }
                        }


                    }
                    cmd.ExecuteNonQuery();
                    if ((i + 1) % 10 == 0)
                    {
                        SBOApp.StatusBar.SetText(string.Format("正在导出 {0}/{1}...", i + 1, oRs.RecordCount), SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
                    }

                    if (i < oRs.RecordCount)
                        oRs.MoveNext();
                }
                bRet = true;
            }
            catch (Exception er)
            {
                SBOApp.StatusBar.SetText(string.Format("异常...{0}", er.Message), SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Error);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                conn.Dispose();

            }
            if (bRet)
            {
                xlApp = new Excel.Application();
                workbook = xlApp.Workbooks._Open(fileName);
                worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1    

                #region 标题 1
                worksheet.Cells[1, 1] = "reference to origin data";
                worksheet.Cells[1, 2] = @"can't contain ""space bar"",must be fill";
                worksheet.Cells[1, 3] = @"if multi-item in same HAWB, connect with "" / """;
                worksheet.Cells[1, 4] = @"if multi-item in same HAWB, connect with "" / """;
                worksheet.Cells[1, 5] = "full name,capital letters ";
                worksheet.Cells[1, 6] = "full name,capital letters ";
                worksheet.Cells[1, 7] = "integer number  (sum by HAWB)";
                worksheet.Cells[1, 8] = @"3 digital after "".""(sum by HAWB)";
                worksheet.Cells[1, 9] = @"3 digital after "".""(sum by HAWB)";
                worksheet.Cells[1, 10] = "if exist, must be fill";
                worksheet.Cells[1, 11] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 12] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 13] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 14] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 15] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 16] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 17] = " fill if exist";
                worksheet.Cells[1, 18] = @"must be fill(if no PO then ""N/A"")";
                worksheet.Cells[1, 19] = "must be fill(complete DN No.)";
                worksheet.Cells[1, 20] = @"2 digital after "".""";
                worksheet.Cells[1, 21] = "optional by drop down list";
                worksheet.Cells[1, 22] = "optional by drop down list";
                worksheet.Cells[1, 23] = "individual by DN";
                worksheet.Cells[1, 24] = "SEDL information";
                worksheet.Cells[1, 25] = "?";
                worksheet.Cells[1, 26] = "?";
                worksheet.Cells[1, 27] = "?";
                worksheet.Cells[1, 28] = "?";
                worksheet.Cells[1, 29] = "no use now ";
                worksheet.Cells[1, 30] = "?";
                worksheet.Cells[1, 31] = "?";
                worksheet.Cells[1, 32] = "?";
                worksheet.Cells[1, 33] = "?";
                worksheet.Cells[1, 34] = "?";
                worksheet.Cells[1, 35] = "optional by drop down list";
                worksheet.Cells[1, 36] = "optional by drop down list";
                worksheet.Cells[1, 37] = "integer number";
                worksheet.Cells[1, 38] = @"2 digital after "".""";
                worksheet.Cells[1, 39] = "optional by drop down list";
                worksheet.Cells[1, 40] = "optional by drop down list";
                worksheet.Cells[1, 41] = "optional by drop down list";
                worksheet.Cells[1, 42] = "optional by drop down list";
                worksheet.Cells[1, 43] = "NEW ADD(drop down list)";
                worksheet.Cells[1, 44] = "NEW ADD(no restriction)";
                worksheet.Cells[1, 45] = "NEW ADD(no restriction)";
                worksheet.Cells[1, 46] = "NEW ADD(drop down list)";
                worksheet.Cells[1, 47] = "VENDOR ID";
                worksheet.Cells[1, 48] = "format: YYYY-MM-DD";
                #endregion
                #region 内容区域
                range = worksheet.get_Range("A3", string.Format("AV{0}", recordCount + 10));
                range.Font.Name = "Arial";
                range.Font.Size = 10;
                range.Borders.LineStyle = 1;
                //range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;  水平对齐方式
                range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic);
                #endregion
                #region 数字、日期 格式
                string[] cols_digital = { "H", "I", "T" };
                string[] cols_dates = { "J", "K", "L", "M", "N", "O", "P" };
                foreach (var item_col in cols_digital)
                {
                    range = worksheet.get_Range(string.Format("{0}3", item_col), string.Format("{0}{1}", item_col, recordCount + 10));
                    range.NumberFormat = @"0.00";
                }
                foreach (var item_col in cols_dates)
                {
                    range = worksheet.get_Range(string.Format("{0}3", item_col), string.Format("{0}{1}", item_col, recordCount + 10));
                    range.NumberFormat = @"yyyy-MM-dd";
                }
                #endregion

                workbook.Save();
                worksheet = null;
                workbook = null;
                xlApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出 
                xlApp = null;

                this.CurrentForm.Items.Item("expxlsx").Enabled = true;
                #region 测试代码
                System.DateTime dt2 = DateTime.Now;
                System.TimeSpan ts1 = dt2 - dt1;

                SBOApp.StatusBar.SetText(string.Format("excel路径：{0}; 导出完成.耗时：{1}min {2} s.", fileName, (int)ts1.TotalMinutes, ts1.Seconds), SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
                #endregion
            }
        }
        private void ExportXML()
        {
            #region 测试代码
            System.DateTime dt1 = DateTime.Now;
            #endregion
            this.CurrentForm.Items.Item("expxml").Enabled = false;
            XmlDocument xmlDoc = new XmlDocument();
            string fileName = string.Format(@"{0}\Seajet template_V013air freight_{1}.xml", System.Windows.Forms.Application.StartupPath, DateTime.Now.ToString("yyyy-MM-dd hhmm"));
            //System.IO.FileStream xmlfileStream = new System.IO.FileStream(fileName, System.IO.FileMode.OpenOrCreate);
            //xmlDoc.Load(xmlfileStream);

            xmlDoc.CreateXmlDeclaration("1.0", "utf-8", null);
            XmlElement root = xmlDoc.CreateElement("", "ExchangeData", "");
            XmlNode Node_lines = xmlDoc.CreateElement("lines");
            xmlDoc.AppendChild(root);
            XmlNode Node_line = default(XmlNode);

            string SQL_ConditionDate = this.GetConditionofCreateDate();
            SAPbobsCOM.Recordset oRs = SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
            oRs.DoQuery(@"SELECT DISTINCT
                                    U_MBLNumber,U_SBLNumber,U_Flight,U_ContainerNo,UPPER(U_StartPort) AS U_StartPort,
UPPER(U_StartPortENamel) AS U_StartPortENamel,U_Quantity,U_GrossWeight,U_BillingWeight,U_PickupDate,
                                   U_ETD,U_ATD,U_ETA,U_ATA,U_ReleaseDate,
                                   U_DeliveryDate,UPPER(U_Sender) as U_Sender,
CASE U_Forwarder 
WHEN N'MUELLER+PARTNER GMBH' THEN N'M+P' 
WHEN N'HELLMANN WORLDWIDE LOGISTICS GMBH & CO KG'  THEN N'HELLMANN'
ELSE U_Forwarder END AS U_Forwarder,
U_TransMode,U_TradeMode,
                                   U_ServiceType,U_CargoType,U_CusBroker,U_CustomClearClass,U_Incoterm,
                                   U_EndPortThCode,
                                   T2.U_PONo,T2.U_DNNo,T2.U_DNCurrency,T2.U_DNValue
                                   FROM [@AVA_SEDL] T1 LEFT JOIN [@AVA_SEDL1] T2 ON T1.DocEntry=T2.DocEntry " + SQL_ConditionDate + " ORDER BY T1.U_PickupDate,T1.U_MBLNumber,T1.U_SBLNumber "
                                 );
            string[] ColUIDIndex ={
                "U_MBLNumber-MAWB","U_SBLNumber-HAWB","U_Flight-Flight","U_ContainerNo-Container_No","U_StartPortENamel-Origin",
                "U_StartPort-Departure","U_Quantity-Pieces","U_GrossWeight-GW","U_BillingWeight-CW","U_PickupDate-Pick_up_Date",
                "U_ETD-ETD","U_ATD-ATD","U_ETA-ETA","U_ATA-ATA","U_ReleaseDate-RELEASE_DATE",
                "U_DeliveryDate-DELIVERY_DATE","U_Sender-VENDOR","U_PONo-PONo","U_DNNo-DNNo","U_DNValue-DNValue",
                "U_Forwarder-FORWARDER","U_TransMode-TRANS_MODE","U_TradeMode-TRADE_MODE", "U_ServiceType-ServiceClass","U_CargoType-GdsNatu",
                "U_CusBroker-Cus_Broker","U_CustomClearClass-CustomClearClass","U_DNCurrency-Currency","U_Incoterm-Incoterm","U_EndPortThCode-Destin"
                  };

            XmlNode node_info = default(XmlNode);
            object fieldValue = default(object);
            for (int i = 0; i < oRs.RecordCount; i++)
            {
                Node_line = xmlDoc.CreateElement("line");
                for (int j = 0; j < ColUIDIndex.Length; j++)
                {
                    node_info = xmlDoc.CreateElement(ColUIDIndex[j].Split('-')[1]);
                    fieldValue = oRs.Fields.Item(ColUIDIndex[j].Split('-')[0]).Value;
                    if (fieldValue.GetType() == typeof(double))
                    {
                        node_info.InnerText = string.Format("{0:F2}", fieldValue);
                    }
                    else if (fieldValue.GetType() == typeof(DateTime))
                    {
                        node_info.InnerText = string.Format("{0:yyyy-MM-dd}", fieldValue);
                    }
                    else
                    {
                        node_info.InnerText = fieldValue.ToString();
                    }

                    Node_line.AppendChild(node_info);

                }
                Node_lines.AppendChild(Node_line);
                if ((i + 1) % 10 == 0)
                {
                    SBOApp.StatusBar.SetText(string.Format("正在导出 {0}/{1}...", i + 1, oRs.RecordCount), SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
                }

                if (i < oRs.RecordCount)
                    oRs.MoveNext();
            }
            root.AppendChild(Node_lines);
            xmlDoc.Save(fileName);

            this.CurrentForm.Items.Item("expxml").Enabled = true;
            #region 测试代码
            System.DateTime dt2 = DateTime.Now;
            System.TimeSpan ts1 = dt2 - dt1;

            SBOApp.StatusBar.SetText(string.Format("xml路径：{0}; 导出完成；耗时：{1}min {2} s.", fileName, (int)ts1.TotalMinutes, ts1.Seconds), SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
            #endregion
        }
        private void ExportReleaseTimeExcelFile()
        {
            #region 测试代码
            System.DateTime dt1 = DateTime.Now;
            #endregion
            Excel.Application xlApp = new Excel.Application();
            if (xlApp == null)
            {
                SBOApp.MessageBox("未安装Excel");
                return;
            }
            SBOApp.StatusBar.SetText("正在创建文件 ...", SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Warning);
            this.CurrentForm.Items.Item("expRxls").Enabled = false;

            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1    
            Excel.Range range = worksheet.get_Range("A1", "AZ2");
            range.Font.Name = "Arial";
            range.Font.Size = 10;
            range.Font.Bold = true;
            range.Cells.Interior.Color = 16777164;//"#CCFFFF";
            range.WrapText = true;
            range.Borders.LineStyle = 1;
            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic);

            #region 标题 2
            worksheet.Cells[2, 1] = "MAWB";
            worksheet.Cells[2, 2] = "HAWB";
            worksheet.Cells[2, 3] = "Flight";
            worksheet.Cells[2, 4] = " Container No.	";
            worksheet.Cells[2, 5] = "Origin";
            worksheet.Cells[2, 6] = "Departure";
            worksheet.Cells[2, 7] = "Pieces";
            worksheet.Cells[2, 8] = "GW";
            worksheet.Cells[2, 9] = "CW";
            worksheet.Cells[2, 10] = "Pick up Date";
            worksheet.Cells[2, 11] = "ETD";
            worksheet.Cells[2, 12] = "ATD";
            worksheet.Cells[2, 13] = "ETA";
            worksheet.Cells[2, 14] = "ATA";
            worksheet.Cells[2, 15] = "RELEASE DATE";
            worksheet.Cells[2, 16] = "DELIVERY  DATE";
            worksheet.Cells[2, 17] = "VENDOR";
            worksheet.Cells[2, 18] = "PO No.";
            worksheet.Cells[2, 19] = "DN No.";
            worksheet.Cells[2, 20] = "DN VALUE";
            worksheet.Cells[2, 21] = "FORWARDER";
            worksheet.Cells[2, 22] = "TRANS-MODE";
            worksheet.Cells[2, 23] = "TRADE MODE";
            worksheet.Cells[2, 24] = "REMARK(SEDL)";
            worksheet.Cells[2, 25] = "CDF NO.";
            worksheet.Cells[2, 26] = "DUTY";
            worksheet.Cells[2, 27] = "VAT";
            worksheet.Cells[2, 28] = "FREIGHT CHARGE";
            worksheet.Cells[2, 29] = "INVOICE NO.";
            worksheet.Cells[2, 30] = "POS NO.";
            worksheet.Cells[2, 31] = "TERMINAL CHARGE	";
            worksheet.Cells[2, 32] = "STORAGE/SERVICE FEE	";
            worksheet.Cells[2, 33] = "TRANSPORT";
            worksheet.Cells[2, 34] = "OTHER";
            worksheet.Cells[2, 35] = "ServiceClass";
            worksheet.Cells[2, 36] = "GdsNatu";
            worksheet.Cells[2, 37] = "FCL(TEU)";
            worksheet.Cells[2, 38] = "LCL(CBM)";
            worksheet.Cells[2, 39] = "Cus Broker";
            worksheet.Cells[2, 40] = "CustomClearClass";
            worksheet.Cells[2, 41] = "Currency";
            worksheet.Cells[2, 42] = "Incoterm";
            worksheet.Cells[2, 43] = "Destin";
            worksheet.Cells[2, 44] = "Remark(SeaJet)";
            worksheet.Cells[2, 45] = "Delete(HAWB)";
            worksheet.Cells[2, 46] = "Delete(DN)";
            worksheet.Cells[2, 47] = "Ready for Clearance Date";
            worksheet.Cells[2, 48] = "Ready for Clearance Time";
            worksheet.Cells[2, 49] = "Release Time";
            worksheet.Cells[2, 50] = "Delivery Time";
            worksheet.Cells[2, 51] = "ATA Time";
            worksheet.Cells[2, 52] = "Sign for people";
            #endregion

            string fileName = string.Format(@"{0}\有调单时间的表_{1}.xls", System.Windows.Forms.Application.StartupPath, DateTime.Now.ToString("yyyyMMdd_HHmm"));
            workbook.SaveCopyAs(fileName);

            int recordCount = 0;
            string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;' ", fileName);
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            OleDbCommand cmd = null;
            bool bRet = false;
            DateTime date1900 = Convert.ToDateTime("1900-01-01");
            List<string> fields_empty = new List<string>() { "U_Flight", "U_ContainerNo", "U_StartPort", "U_StartPortENamel", "U_Quantity", "U_GrossWeight", "U_BillingWeight" };
            List<string> List_SBLNumber = new List<string>();
            string SBLNumber = string.Empty;
            bool isExistsHAWB = false;
            try
            {
                string[] ColUIDIndex ={
                "U_MBLNumber","U_SBLNumber","U_Flight","U_ContainerNo", "U_StartPortENamel",
               "U_StartPort","U_Quantity","U_GrossWeight","U_BillingWeight","U_PickupDate",
                "U_ETD","U_ATD","U_ETA","U_ATA","U_ReleaseDate",
                "U_DeliveryDate","U_Sender","U_Forwarder","U_TransMode","U_TradeMode",
                "U_ServiceType","U_CargoType","U_CusBroker","U_CustomClearClass","U_Incoterm",
                "U_EndPortThCode","U_PONo","U_DNNo","U_DNCurrency","U_DNValue",
                "U_RCDate","U_RCTime","U_ClearedTime","U_DeliveryTime","U_ATATime",
                "U_Signer"
                  };

                string SQL_ConditionDate = this.GetConditionofCreateDate();
                SAPbobsCOM.Recordset oRs = SBOCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
                oRs.DoQuery(@"SELECT 
T1.U_MBLNumber,T1.U_SBLNumber,T1.U_Flight,T1.U_ContainerNo,UPPER(T1.U_StartPort) AS U_StartPort,
UPPER(T1.U_StartPortENamel) AS U_StartPortENamel,T1.U_Quantity,T1.U_GrossWeight,T1.U_BillingWeight,T1.U_PickupDate,
T1.U_ETD,T1.U_ATD,T1.U_ETA,T1.U_ATA,T1.U_ReleaseDate,
T1.U_DeliveryDate,UPPER(T1.U_Sender) as U_Sender,
CASE U_Forwarder 
WHEN N'MUELLER+PARTNER GMBH' THEN N'M+P' 
WHEN N'HELLMANN WORLDWIDE LOGISTICS GMBH & CO KG'  THEN N'HELLMANN'
ELSE T1.U_Forwarder END AS U_Forwarder,
T1.U_TransMode,T1.U_TradeMode,
T1.U_ServiceType,T1.U_CargoType,T1.U_CusBroker,T1.U_CustomClearClass,T1.U_Incoterm,
T1.U_EndPortThCode,
T2.U_PONo,T2.U_DNNo,T2.U_DNCurrency,T2.U_DNValue,
T1.U_RCDate,
LEFT(CAST(T1.U_RCTime AS NVARCHAR(20)),LEN(CAST(T1.U_RCTime AS NVARCHAR(20)))-2)+N':'+ RIGHT(CAST(T1.U_RCTime AS NVARCHAR(20)),2) as U_RCTime,
LEFT(CAST(T1.U_ClearedTime AS NVARCHAR(20)),LEN(CAST(T1.U_ClearedTime AS NVARCHAR(20)))-2)+N':'+ RIGHT(CAST(T1.U_ClearedTime AS NVARCHAR(20)),2) as U_ClearedTime,
LEFT(CAST(T1.U_DeliveryTime AS NVARCHAR(20)),LEN(CAST(T1.U_DeliveryTime AS NVARCHAR(20)))-2)+N':'+ RIGHT(CAST(T1.U_DeliveryTime AS NVARCHAR(20)),2) as U_DeliveryTime,
LEFT(CAST(T1.U_ATATime AS NVARCHAR(20)),LEN(CAST(T1.U_ATATime AS NVARCHAR(20)))-2)+N':'+ RIGHT(CAST(T1.U_ATATime AS NVARCHAR(20)),2) as U_ATATime,
T3.U_Signer

FROM [@AVA_SEDL] T1 LEFT JOIN [@AVA_SEDL1] T2 ON T1.DocEntry=T2.DocEntry 
LEFT JOIN [@AVA_FF_OBNO] T3 ON T1.U_SBLNumber=T3.U_SBLNumber
" + SQL_ConditionDate + " ORDER BY T1.U_PickupDate,T1.U_MBLNumber,T1.U_SBLNumber "
                 );

                conn.Open();
                cmd = new OleDbCommand(@"create table [Sheet1$](
                    U_MBLNumber Text,U_SBLNumber Text,U_Flight Text,U_ContainerNo Text,U_StartPortENamel Text,
U_StartPort Text,U_Quantity int,U_GrossWeight double,U_BillingWeight double,U_PickupDate Date,
U_ETD Date,U_ATD Date,U_ETA Date,U_ATA Date,U_ReleaseDate Date,
U_DeliveryDate Date,U_Sender Text,	U_PONo Text,U_DNNo Text,U_DNValue double,	
U_Forwarder Text,U_TransMode Text,U_TradeMode Text,	[REMARK(SEDL)] Text,[CDF NO.] Text,	
[DUTY] Text,	[VAT] double,	[FREIGHT CHARGE] Text,	[INVOICE NO.] Text,	[POS NO.] Text,	
[TERMINAL CHARGE] Text,	[STORAGE/SERVICE FEE] Text,[TRANSPORT] Text,	[OTHER] Text,	U_ServiceType Text,
U_CargoType Text,	[FCL(TEU)] Text,	[LCL(CBM)] Text,U_CusBroker Text,U_CustomClearClass Text,
U_DNCurrency Text,U_Incoterm Text,U_EndPortThCode Text,[Remark(SeaJet)] Text,[Delete(HAWB)] Text,	
[Delete(DN)] Text,
U_RCDate Date,
U_RCTime Text,
U_ClearedTime Text,
U_DeliveryTime Text,
U_ATATime Text,
U_Signer Text
                    )", conn);
                cmd.ExecuteNonQuery();

                string strSQL = @"INSERT INTO [Sheet1$] (
U_MBLNumber,U_SBLNumber,U_Flight,U_ContainerNo,U_StartPortENamel,
U_StartPort,U_Quantity,U_GrossWeight,U_BillingWeight,U_PickupDate,
U_ETD,U_ATD,U_ETA,U_ATA,U_ReleaseDate,
U_DeliveryDate,U_Sender,U_Forwarder,U_TransMode,U_TradeMode,
U_ServiceType,U_CargoType,U_CusBroker,U_CustomClearClass,U_Incoterm,
U_EndPortThCode,U_PONo,U_DNNo,U_DNCurrency,U_DNValue,U_RCDate,U_RCTime,U_ClearedTime,U_DeliveryTime,U_ATATime,U_Signer) VALUES (
?, ?, ?,?,?,
?, ?, ?,?,?,
?, ?, ?,?,?,
?, ?, ?,?,?,
?, ?, ?,?,?,
?, ?, ?,?,?,
?, ?, ?,?,?,
?
)";
                cmd = new OleDbCommand(strSQL, conn);
                foreach (var item in ColUIDIndex)
                {
                    cmd.Parameters.Add(item, OleDbType.VarChar);
                }
                recordCount = oRs.RecordCount;
                for (int i = 0; i < oRs.RecordCount; i++)
                {
                    isExistsHAWB = true;
                    SBLNumber = oRs.Fields.Item("U_SBLNumber").Value;
                    if (!List_SBLNumber.Contains(SBLNumber))
                    {
                        List_SBLNumber.Add(SBLNumber);
                        isExistsHAWB = false;
                    }
                    for (int j = 0; j < ColUIDIndex.Length; j++)
                    {
                        if (isExistsHAWB && fields_empty.Contains(ColUIDIndex[j]))
                        {
                            cmd.Parameters[j].Value = DBNull.Value;
                        }
                        else
                        {
                            if (oRs.Fields.Item(ColUIDIndex[j]).Value.GetType() == typeof(DateTime))
                            {
                                if (oRs.Fields.Item(ColUIDIndex[j]).Value <= date1900)
                                {
                                    cmd.Parameters[j].Value = DBNull.Value;
                                }
                                else
                                {
                                    cmd.Parameters[j].Value = oRs.Fields.Item(ColUIDIndex[j]).Value;
                                }
                            }
                            else
                            {
                                cmd.Parameters[j].Value = oRs.Fields.Item(ColUIDIndex[j]).Value;
                            }
                        }


                    }
                    cmd.ExecuteNonQuery();
                    if ((i + 1) % 10 == 0)
                    {
                        SBOApp.StatusBar.SetText(string.Format("正在导出 {0}/{1}...", i + 1, oRs.RecordCount), SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
                    }

                    if (i < oRs.RecordCount)
                        oRs.MoveNext();
                }
                bRet = true;
            }
            catch (Exception er)
            {
                SBOApp.StatusBar.SetText(string.Format("异常...{0}", er.Message), SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Error);
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                conn.Dispose();

            }
            if (bRet)
            {
                xlApp = new Excel.Application();
                workbook = xlApp.Workbooks._Open(fileName);
                worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1    

                #region 标题 1
                worksheet.Cells[1, 1] = "reference to origin data";
                worksheet.Cells[1, 2] = @"can't contain ""space bar"",must be fill";
                worksheet.Cells[1, 3] = @"if multi-item in same HAWB, connect with "" / """;
                worksheet.Cells[1, 4] = @"if multi-item in same HAWB, connect with "" / """;
                worksheet.Cells[1, 5] = "full name,capital letters ";
                worksheet.Cells[1, 6] = "full name,capital letters ";
                worksheet.Cells[1, 7] = "integer number  (sum by HAWB)";
                worksheet.Cells[1, 8] = @"3 digital after "".""(sum by HAWB)";
                worksheet.Cells[1, 9] = @"3 digital after "".""(sum by HAWB)";
                worksheet.Cells[1, 10] = "if exist, must be fill";
                worksheet.Cells[1, 11] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 12] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 13] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 14] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 15] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 16] = "format: YYYY-MM-DD";
                worksheet.Cells[1, 17] = " fill if exist";
                worksheet.Cells[1, 18] = @"must be fill(if no PO then ""N/A"")";
                worksheet.Cells[1, 19] = "must be fill(complete DN No.)";
                worksheet.Cells[1, 20] = @"2 digital after "".""";
                worksheet.Cells[1, 21] = "optional by drop down list";
                worksheet.Cells[1, 22] = "optional by drop down list";
                worksheet.Cells[1, 23] = "individual by DN";
                worksheet.Cells[1, 24] = "SEDL information";
                worksheet.Cells[1, 25] = "?";
                worksheet.Cells[1, 26] = "?";
                worksheet.Cells[1, 27] = "?";
                worksheet.Cells[1, 28] = "?";
                worksheet.Cells[1, 29] = "no use now ";
                worksheet.Cells[1, 30] = "?";
                worksheet.Cells[1, 31] = "?";
                worksheet.Cells[1, 32] = "?";
                worksheet.Cells[1, 33] = "?";
                worksheet.Cells[1, 34] = "?";
                worksheet.Cells[1, 35] = "optional by drop down list";
                worksheet.Cells[1, 36] = "optional by drop down list";
                worksheet.Cells[1, 37] = "integer number";
                worksheet.Cells[1, 38] = @"2 digital after "".""";
                worksheet.Cells[1, 39] = "optional by drop down list";
                worksheet.Cells[1, 40] = "optional by drop down list";
                worksheet.Cells[1, 41] = "optional by drop down list";
                worksheet.Cells[1, 42] = "optional by drop down list";
                worksheet.Cells[1, 43] = "NEW ADD(drop down list)";
                worksheet.Cells[1, 44] = "NEW ADD(no restriction)";
                worksheet.Cells[1, 45] = "NEW ADD(no restriction)";
                worksheet.Cells[1, 46] = "NEW ADD(drop down list)";

                #endregion
                #region 内容区域
                range = worksheet.get_Range("A3", string.Format("AZ{0}", recordCount + 10));
                range.Font.Name = "Arial";
                range.Font.Size = 10;
                range.Borders.LineStyle = 1;
                //range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;  水平对齐方式
                range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexAutomatic);
                #endregion
                #region 数字、日期 格式
                string[] cols_digital = { "H", "I", "T" };
                string[] cols_dates = { "J", "K", "L", "M", "N", "O", "P", "AV" };
                foreach (var item_col in cols_digital)
                {
                    range = worksheet.get_Range(string.Format("{0}3", item_col), string.Format("{0}{1}", item_col, recordCount + 10));
                    range.NumberFormat = @"0.00";
                }
                foreach (var item_col in cols_dates)
                {
                    range = worksheet.get_Range(string.Format("{0}3", item_col), string.Format("{0}{1}", item_col, recordCount + 10));
                    range.NumberFormat = @"yyyy-MM-dd";
                }
                #endregion

                workbook.Save();
                worksheet = null;
                workbook = null;
                xlApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出 
                xlApp = null;

                this.CurrentForm.Items.Item("expRxls").Enabled = true;
                #region 测试代码
                System.DateTime dt2 = DateTime.Now;
                System.TimeSpan ts1 = dt2 - dt1;

                SBOApp.StatusBar.SetText(string.Format("excel路径：{0}; 导出完成.耗时：{1}min {2} s.", fileName, (int)ts1.TotalMinutes, ts1.Seconds), SAPbouiCOM.BoMessageTime.bmt_Medium, SAPbouiCOM.BoStatusBarMessageType.smt_Success);
                #endregion
            }
        }
        #endregion
    }
}
